<?php
/**
* 
*/
class SurveyModel extends MY_Model
{
	
	public function __construct()
	{
		parent::__construct();
		$this->table = 'nmp_user';
	}

	//获取会员
	public function analyse_member()
	{
		//获取今日注册会员
		$start = date('Y-m-d 00:00:00');
		$end = date('Y-m-d H:i:s');
		$query = $this->db->query("SELECT count(*) as d_num FROM ".$this->table." WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end')");
		$res = $query->row_array();
		$data['d_num'] = $res['d_num'];
		

		
		$start = date("Y-m-d 00:00:00",strtotime("-1 day"));
		$end   = date('Y-m-d 23:59:59',strtotime("-1 day"));
		
		$start_l = strtotime($start);
		$end_l   = strtotime($end);
		//获取昨日
		$query = $this->db->query("SELECT count(*) as ys_num FROM ".$this->table." WHERE addtime >= '{$start_l}' AND addtime <= '{$end_l}' ");
		$res = $query->row_array();
		$data['ys_num'] = $res['ys_num'];
		
		//获取本周
		
		$query = $this->db->query("SELECT count(*) as w_num FROM ".$this->table." WHERE WEEKOFYEAR(FROM_UNIXTIME(addtime, '%Y-%m-%d %H:%i:%s') ) = WEEKOFYEAR(now())");
		$res = $query->row_array();
		$data['w_num'] = $res['w_num'];
		//获取本月
		$start = date('Y-m-01 00:00:00');
		$end = date('Y-m-d H:i:s');
		$query = $this->db->query("SELECT count(*) as m_num FROM ".$this->table." WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end')");
		$res = $query->row_array();
		$data['m_num'] = $res['m_num'];
		
		
		
		//今日付费会员
		$query = $this->db->query("SELECT count(distinct(user_id)) as d_num2 FROM nmp_user_card_log WHERE addtime >= unix_timestamp('{$start}') AND addtime <= unix_timestamp('{$end}')");
		$res = $query->row_array();
		$data['d_num2'] = $res['d_num2'];
		//昨日付费
		
		$query = $this->db->query("SELECT count(distinct(user_id)) as ys_num2 FROM nmp_user_card_log WHERE addtime >= '{$start_l}' AND addtime <= '{$end_l}' ");
		$res = $query->row_array();
		$data['ys_num2'] = $res['ys_num2'];
		//本周付费
		$query = $this->db->query("SELECT count(distinct(user_id)) as w_num2 FROM nmp_user_card_log WHERE WEEKOFYEAR(FROM_UNIXTIME(addtime, '%Y-%m-%d %H:%i:%s') ) = WEEKOFYEAR(now())");
		$res = $query->row_array();
		$data['w_num2'] = $res['w_num2'];
		
		
		//本月付费
		$query = $this->db->query("SELECT count(distinct(user_id)) as m_num2 FROM nmp_user_card_log WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end')");
		$res = $query->row_array();
		$data['m_num2'] = $res['m_num2'];

		//场馆转化率
		$query = $this->db->query("SELECT count(*) member_num FROM ".$this->table);
		$res = $query->row_array();
		$data['member_num'] = $res['member_num'];

		//付费
		$query = $this->db->query("SELECT count(distinct(user_id)) as vip_member FROM nmp_user_card_log");
		$res = $query->row_array();
		$data['vip_num'] = $res['vip_member'];

		$data['vip_percen'] = round($data['vip_num']/$data['member_num']*100)."％";

		//续费
		$query = $this->db->query("SELECT id, user_id, card_id FROM nmp_user_card_log AS cl WHERE EXISTS(SELECT user_id FROM nmp_user_card_log WHERE user_id=cl.user_id GROUP BY user_id having count(*)>=2)");
		$res =$query->result_array();

		$arr = array();
		foreach ($res as $key => $value) {
			$arr[$value['user_id']] = $value;
		}
		$data['renew_num'] = count($arr);
		$data['renew_percen'] = round($data['renew_num']/$data['member_num']*100)."％";

		return $data;
	}



	/** 
	*	//获取折线图数据
	* @param $type   查询类型（0为新增会员，1为新增付费，2为付费，3为积累会员）
	* @param $dnum 	 天数
	*/
	public function member_data($type,$dnum)
	{
		$date_arr = array();
		$user_arr = array();

		if ($type == 0 || $type == 1) 
		{
			$this->table = $type== 1 ? 'nmp_user_card_log' : $this->table;
			
			$group = "";
			if($type== 1){
				//$group = " GROUP BY user_id  ";
			}
			
			//判读是天数还是日期
			if (!is_numeric($dnum)) 
			{
				//处理日期
				$d1 = substr($dnum,0,10);
				$d2 = substr($dnum,-10);
				$dlist = $this->getDateFromRange($d1,$d2);

				for ($i=0; $i < count($dlist); $i++) 
				{
					$date_arr[] = substr($dlist[$i],-5);

					$start = date('Y-m-d 00:00:00',strtotime($dlist[$i]));
					$end = date('Y-m-d 00:00:00',strtotime($dlist[$i])+86400);
					
					$query = $this->db->query("SELECT count(*) as user_num FROM ".$this->table." WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end')".$group);
					$res = $query->row_array();

					$user_arr[] = $res['user_num'];
				}
				
			}
			else
			{
				for ($i=$dnum; $i > 0; $i--) 
				{
					$date_arr[] = date('m-d',strtotime('-'.$i.' day'));

					$start = date('Y-m-d 00:00:00',strtotime('-'.$i.' day'));
					$end = date('Y-m-d 00:00:00',strtotime('-'.($i-1).' day'));

					$query = $this->db->query("SELECT count(*) as user_num FROM ".$this->table." WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end')".$group);
					$res = $query->row_array();

					$user_arr[] = $res['user_num'];
				}
			}
		}
		else
		{

			$this->table = $type== 2 ? 'nmp_user_card_log' : $this->table;
			$count = $type== 2 ? 'count(distinct(user_id)) as user_num' : 'count(*) as user_num';

			if (!is_numeric($dnum)) 
			{
				//处理日期
				$d1 = substr($dnum,0,10);
				$d2 = substr($dnum,-10);
				$dlist = $this->getDateFromRange($d1,$d2);

				for ($i=0; $i < count($dlist); $i++) 
				{
					$date_arr[] = substr($dlist[$i],-5);

					$end = date('Y-m-d 00:00:00',strtotime($dlist[$i])+86400);

					$query = $this->db->query("SELECT ".$count." FROM ".$this->table." WHERE addtime <= unix_timestamp('$end')");
					$res = $query->row_array();

					$user_arr[] = $res['user_num'];
				}
			}
			else
			{
				for ($i=$dnum; $i > 0; $i--) 
				{
					$date_arr[] = date('m-d',strtotime('-'.$i.' day'));
					$end = date('Y-m-d 00:00:00',strtotime('-'.($i-1).' day'));

					$query = $this->db->query("SELECT ".$count." FROM ".$this->table." WHERE addtime <= unix_timestamp('$end')");
					$res = $query->row_array();

					$user_arr[] = $res['user_num'];
				}

			}

		}

		$data['date_list'] = $date_arr;
		$data['user_num'] = $user_arr;

		return $data;

	}


	/**
	 * 获取指定日期段内每一天的日期
	 * @param  Date  $startdate 开始日期
	 * @param  Date  $enddate   结束日期
	 * @return Array
	 */
	function getDateFromRange($startdate, $enddate)
	{
	    $stimestamp = strtotime($startdate);
	    $etimestamp = strtotime($enddate);

	    // 计算日期段内有多少天
	    $days = ($etimestamp-$stimestamp)/86400+1;

	    // 保存每天日期
	    $date = array();
	    for($i=0; $i<$days; $i++){
	        $date[] = date('Y-m-d', $stimestamp+(86400*$i));
	    }

	    return $date;
	}



	//会员付费
	public function user_consu_data()
	{
		$query = $this->db->query("SELECT count(*) as member_num FROM ".$this->table);
		$res = $query->row_array();
		$member_num = $res['member_num'];

		$query = $this->db->query("SELECT count(distinct(user_id)) as vip_num FROM nmp_user_card_log");
		$res = $query->row_array();
		$vip_num = $res['vip_num'];

		//未付费人数
		$ord_num = $member_num - $vip_num;

		$arr[] = array('付费',intval($vip_num));
		$arr[] = array('潜在',$ord_num);

		return $arr;
	}


	//会员性别
	public function user_sex_data()
	{
		$query = $this->db->query("SELECT sex FROM ".$this->table);
		$res = $query->result_array();

		$man_num = 0;
		$girl_num = 0;
		$x_num = 0;
		foreach ($res as $key => $value) 
		{
			if ($value['sex'] == 1) {
				$man_num = $man_num + 1;
			}elseif ($value['sex'] == 2) {
				$girl_num = $girl_num + 1;
			}else{
				$x_num = $x_num + 1;
			}
		}

		$arr[] = array('男',$man_num);
		$arr[] = array('女',$girl_num);
		$arr[] = array('其他',$x_num);

		return $arr;

	}


	//会员年龄分布
	public function user_age_data($sex)
	{
		$query = $this->db->query("SELECT birthday FROM ".$this->table." WHERE sex = ".$sex);
		$res = $query->result_array();

		$n1 = 0;$n2 = 0;$n3 = 0;
		$n4 = 0;$n5 = 0;$n6 = 0;$n7 = 0;
		foreach ($res as $key => $value) 
		{
			$age = $this->calcAge($value['birthday']);

			if ($age < 18) {
				$n1 = $n1 + 1;
			}elseif ($age >= 18 && $age <= 24) {
				$n2 = $n2 + 1;
			}elseif ($age >= 25 && $age <= 30) {
				$n3 = $n3 + 1;
			}elseif ($age >= 31 && $age <= 40) {
				$n4 = $n4 + 1;
			}elseif ($age >= 41 && $age <= 50) {
				$n5 = $n5 + 1;
			}elseif ($age >= 51 && $age <= 60) {
				$n6 = $n6 + 1;
			}elseif ($age >= 61) {
				$n7 = $n7 + 1;
			}
		}
		return array($n1,$n2,$n3,$n4,$n5,$n6,$n7);
	}


	/**
	 * 根据出生日期算出年龄
	 * @param  Date  $birthday 日期
	 * @return int
	 */
	public function calcAge($birthday) 
	{
    	$age = 0;
    	if(!empty($birthday)){
    		$age = strtotime($birthday);
    		if($age === false){
    			return 0;
    		}
    		
    		list($y1,$m1,$d1) = explode("-",date("Y-m-d", $age));
    		
    		list($y2,$m2,$d2) = explode("-",date("Y-m-d"), time());
    		
    		$age = $y2 - $y1;
    		if((int)($m2.$d2) < (int)($m1.$d1)){
    			$age -= 1;
    		}
    	}
    	return $age;
	}




	//会员预约信息
	public function user_appoin_data()
	{
		$query = $this->db->query("SELECT id FROM ".$this->table);
		$user = $query->result_array();

		$start = date('Y-m-d 00:00:00',strtotime('-30 day'));
		$end = date('Y-m-d 00:00:00');

		$n1 = 0;$n2 = 0;$n3 = 0;$n4 = 0;$n5 = 0;
		foreach ($user as $key => $value) 
		{
			$query = $this->db->query("SELECT count(*) as num FROM nmp_user_appoint WHERE addtime >= unix_timestamp('$start') AND addtime <= unix_timestamp('$end') AND user_id = '$value[id]'");
			$app = $query->row_array();
			$num = $app['num'];

			if ($num == 0) {
				$n1 = $n1 + 1;
			}elseif ($num >=1 && $num <= 5) {
				$n2 = $n2 + 1;
			}elseif ($num >= 6 && $num <= 10) {
				$n3 = $n3 + 1;
			}elseif ($num >= 11 && $num <= 15) {
				$n4 = $n4 + 1;
			}elseif ($num >= 16) {
				$n5 = $n5 + 1;
			}
		}

		return array($n1,$n2,$n3,$n4,$n5);

	}




}
?>